package com.itqn.dao.Impl;

import cn.hutool.core.util.StrUtil;
import com.itqn.dao.MemberDao;
import com.itqn.pojo.Member;
import lombok.SneakyThrows;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class MemberDaoImpl implements MemberDao {
    ResultSet rs;

    @Override
    public Member findById(int id) throws Exception {
        Member member = null;
        BaseDao baseDao = new BaseDao();
        String sql = "SELECT id,`name`,phone,cart_number,balance,total_amount,`status`,create_time FROM `member` where id=?";
        rs = baseDao.getlist(sql, new Object[]{id});
        while (rs.next()) {
            member = new Member();
            member.setId(rs.getLong("id"));
            member.setName(rs.getString("name"));
            member.setPhone(rs.getString("phone"));
            member.setCartNumber(rs.getString("cart_number"));
            member.setBalance(rs.getBigDecimal("balance"));
            member.setTotalAmount(rs.getBigDecimal("total_amount"));
            member.setStatus(rs.getInt("status"));

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String date = sdf.format(rs.getDate("create_time"));
            String time = sdf.format(rs.getTime("create_time"));
            date = date.substring(0, date.indexOf(" "));
            time = time.substring(time.indexOf(" "));
            member.setCreateTime(date + " " + time);
        }
        return member;
    }

    @Override
    public int addProduct(Member member) throws Exception {
        if (member.getName().equals("") || member.getName() == null) {
            return Integer.parseInt("会员姓名不能为空不能为空");
        } else if (member.getPhone().equals("") || member.getPhone() == null) {
            return Integer.parseInt("手机号不能为空");
        } else if (member.getCartNumber().equals("") || member.getCartNumber() == null) {
            return Integer.parseInt("会员卡号不能为空，将默认以手机号作为卡号");
        } else if (member.getBalance().equals("") || member.getBalance() == null) {
            return Integer.parseInt("储值金额不能为空");
        }
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "INSERT into member (`name`,phone,cart_number,balance,create_time) value (?,?,?,?,?)";
        result = baseDao.update(sql, new Object[]{member.getName(), member.getPhone(), member.getCartNumber(), member.getBalance(), new Date()});
        return result;
    }

    @SneakyThrows
    @Override
    public int updateMember(Member member) {
        int result = 0;
        BaseDao baseDao = new BaseDao();
        String sql = "UPDATE member set `name`=?,phone=?,balance=?,total_amount=? where id=?";
        result = baseDao.update(sql, new Object[]{member.getName(), member.getPhone(), member.getBalance(), member.getTotalAmount(), member.getId()});
        return result;
    }

    @Override
    public List<Member> query(String name, String cartNumber, Integer totalAmount, Integer pageSize, Integer page) throws Exception {

        List<Member> list = new ArrayList<>();
        Member member = null;
        Integer flag = 0;
        BaseDao baseDao = new BaseDao();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT id,`name`,phone,cart_number,balance,total_amount,`status`,create_time FROM `member` ");

        boolean isBlank = StrUtil.isNotBlank(name);
        if (isBlank || StrUtil.isNotBlank(cartNumber) || totalAmount != null) {
            sb.append("where ");
        }
        if (isBlank) {
            flag = 1;
            sb.append("`name` LIKE CONCAT('%',?,'%') ");
        }
        if (StrUtil.isNotBlank(cartNumber)) {
            if (isBlank) {
                flag = 2;
                sb.append("and cart_number LIKE CONCAT('%',?,'%') ");
            } else {
                flag = 3;
                sb.append("cart_number LIKE CONCAT('%',?,'%') ");
            }
        }
        if (totalAmount != null) {
            if (isBlank && StrUtil.isNotBlank(cartNumber)) {
                flag = 6;
                sb.append("and total_amount>? ");
            } else if (StrUtil.isNotBlank(cartNumber)) {
                flag = 5;
                sb.append("and total_amount>? ");
            } else if (isBlank) {
                flag = 4;
                sb.append("and total_amount>? ");
            } else {
                flag = 7;
                sb.append("total_amount>? ");
            }
        }
        Boolean isPage = page != null && pageSize != null;
        if (isPage) {
            sb.append("limit ?, ?");
        }
        switch (flag) {
            case 1:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name});
                }
                break;
            case 2:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, cartNumber, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, cartNumber});
                }
                break;
            case 3:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{cartNumber, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{cartNumber});
                }
                break;
            case 4:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, totalAmount, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, totalAmount});
                }
                break;
            case 5:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{cartNumber, totalAmount, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{cartNumber, totalAmount});
                }
                break;
            case 6:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, cartNumber, totalAmount, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{name, cartNumber, totalAmount});
                }
                break;
            case 7:
                if (isPage) {
                    rs = baseDao.getlist(sb.toString(), new Object[]{totalAmount, (page - 1) * pageSize, pageSize});
                } else {
                    rs = baseDao.getlist(sb.toString(), new Object[]{totalAmount});
                }
                break;
            default:
                rs = baseDao.getlist(sb.toString(), new Object[]{(page - 1) * pageSize, pageSize});
                break;
        }

        while (rs.next()) {
            member = new Member();
            member.setId(rs.getLong("id"));
            member.setName(rs.getString("name"));
            member.setPhone(rs.getString("phone"));
            member.setCartNumber(rs.getString("cart_number"));
            member.setBalance(rs.getBigDecimal("balance"));
            member.setTotalAmount(rs.getBigDecimal("total_amount"));
            member.setStatus(rs.getInt("status"));

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            String date = sdf.format(rs.getDate("create_time"));
            String time = sdf.format(rs.getTime("create_time"));
            date = date.substring(0, date.indexOf(" "));
            time = time.substring(time.indexOf(" "));
            member.setCreateTime(date + " " + time);
            list.add(member);
        }
        return list;
    }

    @Override
    public Integer totalCount(String name, String cartNumber, Integer totalAmount) throws Exception {

        BaseDao baseDao = new BaseDao();
        //编写sql语句
        StringBuilder sb = new StringBuilder();
        sb.append("select count(id) from member ");
        Integer flag = 0;
        boolean isBlank = StrUtil.isNotBlank(name);
        if (isBlank || StrUtil.isNotBlank(cartNumber) || totalAmount != null) {
            sb.append("where ");
        }
        if (isBlank) {
            flag = 1;
            sb.append("`name` LIKE CONCAT('%',?,'%') ");
        }
        if (StrUtil.isNotBlank(cartNumber)) {
            if (isBlank) {
                flag = 2;
                sb.append("and cart_number LIKE CONCAT('%',?,'%') ");
            } else {
                flag = 3;
                sb.append("cart_number LIKE CONCAT('%',?,'%') ");
            }
        }
        if (totalAmount != null) {
            if (isBlank && StrUtil.isNotBlank(cartNumber)) {
                flag = 6;
                sb.append("and total_amount>? ");
            } else if (StrUtil.isNotBlank(cartNumber)) {
                flag = 5;
                sb.append("and total_amount>? ");
            } else if (isBlank) {
                flag = 4;
                sb.append("and total_amount>? ");
            } else {
                flag = 7;
                sb.append("total_amount>? ");
            }
        }
        switch (flag) {
            case 1:
                rs = baseDao.getlist(sb.toString(), new Object[]{name});
                break;
            case 2:
                rs = baseDao.getlist(sb.toString(), new Object[]{name, cartNumber});
                break;
            case 3:
                rs = baseDao.getlist(sb.toString(), new Object[]{cartNumber});
                break;
            case 4:
                rs = baseDao.getlist(sb.toString(), new Object[]{name, totalAmount});
                break;
            case 5:
                rs = baseDao.getlist(sb.toString(), new Object[]{cartNumber, totalAmount});
                break;
            case 6:
                rs = baseDao.getlist(sb.toString(), new Object[]{name, cartNumber, totalAmount});
                break;
            case 7:
                rs = baseDao.getlist(sb.toString(), new Object[]{totalAmount});
                break;
            default:
                rs = baseDao.getlist(sb.toString(), new Object[]{});
                break;
        }
        int number = 0;
        if (rs.next()) {
            number = rs.getInt(1);
        }
        // number是总条数
        return number;
    }

    @Override
    public void updateStatic(Member member) throws Exception {
        BaseDao baseDao = new BaseDao();
        String sql = "UPDATE member set `status`=? where id=?";
        baseDao.update(sql, new Object[]{member.getStatus(), member.getId()});
    }
}
